RPG2SQL Integrator

RPG2SQL Integrator is ready for V6R1/V7R1 of i5/OS, starting with version 1.50

December 2015

Version 1.58.0
  • The minimum OS/400 compatibility is now V6R1

April 2014

Version 1.57
  • Added field delimiter parameter to SQLExtRecDDS API.
  • Syntax: SQLExtRecDDS(INFILE : INLIB : %ADDR(FldDefArray): %ADDR(FldPtrArray): %ADDR(FldDefCount) : FldDelimiter)

The new field is defined as 4096 long, varying and NOPASS. If not passed in it will default to a tilde (~). This will allow current programs to continue to work without need of recompiling. This field should be same value as what is used in the SQL_SetSQLDelim API.

December 2013

Version 1.56
  • Added SQL_Verify API to check a connection
  • Syntax: SQL_Verify(IP_Addr : Port : TimeOut)
    Where:
    • IP_Addr is IP address of PC connecting to in either dot format or name
    • Port is Port to check of PC connecting to (5 char)
    • TimeOut is time to wait in seconds (2 char)
  • Added ability to handle Unicode/Graphic fields to SQLExtRecDDS API

June 2013

Version 1.55
  • Changed SQL_Quote to only trim trailing spaces not both leading and trailing spaces

January 2013

Version 1.54
  • Removed XLS examples that used ODBC. SQTEST04 & SQTEST09 were removed

November 2009

Version 1.53
  • Fixed problem with national language builds, that the source for the main service programs was being converted twice, so that the product would not work at all.
  • Changes to SQLXLSIMP command
    • Changed wording from spreadsheet to workbook or worksheet
    • Added 'From worksheet' parameter - default is *ACTIVE
  • Added SQLXLSEXP (Export PF to Worksheet) command. Parameters include:
    • input file
      • must exist and be either a physical file or single-format logical file
      • special values for library include *LIBL and *CURLIB
    • output workbook
      • name must be valid for the RPG2SQL PC component
      • will be created if it does not exist
    • input member
      • default is *FIRST
    • output worksheet - default is *ACTIVE, the
      • default is *ACTIVE, the sheet that was was open when the workbook was last saved
      • will be added if not found
      • add/replace option included
        • default is *REPLACE
    • header
      • default is *COLHDG, which puts newlines between the parts of a column heading
      • other values are *NONE, *TEST, and *NAME
    • message logging - if there are any errors or warnings, they are sent to the job log; a final message is also sent to QSYSOPR; messages can be found in message file RPSMSGF in RJSRPGSQL. Diagnostic messages include:
      • NULL found
      • too many digits
      • too many characters
      • too many fields requested
      • too many rows requested
      • new workbook/worksheet created/added
    • host - name or address of PC where RPG2SQL PC component is running - special value is *CURRENT, which will be converted to the IP address of the PC on which the terminal emulation is running (does not work in a batch job)
    • first record (not RRN)
      • default is *FIRST
    • last record (not RRN)
      • default is *LAST
    • first field (by number)
      • default is *FIRST
    • last field
      • default is *LAST

September 2008

Version 1.52
  • Fixed problem with commas in row/column values in XLS functions. This was a problem for customers in countries that use the comma as the decimal point.

April 2008

Version 1.51
  • Updated SQL_GetParm to make sure that if there is a '199 EOF' present in the data buffer it gets stripped out automatically.
  • This update requires V1.0.57 or above of RPG2SQL Integrator PC Component.V1.0.57 of the PC component also fixes a lockup issue when calling SQL_GetParm.

January 2008

Version 1.50
  • Updated SQL_GetFldNum and SQL_GetFldNumB functions to handle rounding appropriately. There were some cases where numbers that ended in whole number decimal values were rounding into fractional values when converted to 30,9. The new numeric conversion routine should resolve this issue.
    Example of rounding error:
    • Value of 37294640.90 ~ Rounding to 37294640.899999999
    • Value of 10005654.36 ~ Rounding to 10005654.35999999
NOTE:

Minimum OS/400 release is V5R1.

June 2007

Version 1.49
  • Fixed problem with SQLXLSIMP that it truncated text in cells to 1024 characters. Maximum length is now around 4096 characters.

September 2006

Version 1.48
  • Fixed problem with SQLXLSIMP that it said a number in cell A1 had non-numeric data.

March 2006

Version 1.47
  • Finished SQLXLSIMP (Import Spreadsheet to PF) command. - message logging - if there are any errors or warnings, they are sent to the job log; a final message is also sent to QSYSOPR; messages can be found in message file RPSMSGF in RJSRPGSQL.
    Diagnostic messages include:
    • row skipped due to unexpected error
    • row skipped for blank row
    • row defaulted for blank row
    • numeric overflow
    • text truncated
    • invalid characters in number
    • invalid date/time/timestamp
    • import error (escape message)
  • Added blank row handling - options are *SKIP (default) and *DFT - message logged in either case that identifies the row in the worksheet that was empty - more data type supported - binary, date, time, timestamp, and floating point - numbers with more decimals than the PF field has are truncated without comment
  • Fixed SQL_TCPMultBuff() so that it gets the entire buffer from the socket. Added SQL_TCPMultBuff() to all samples.

February 2006

Version 1.46
  • Added XLS_SetDelimRc2 and XLS_GetDelimRc2, to get and set delimited buffer with 30,000-byte buffers.
  • Added XLS_RowColCount to retrieve row and column info from spreadsheet. Main use is to get number of rows in spreadsheet. Can also be used to get used range. Added SQLXLSIMP (Import Spreadsheet to PF) command. Parameters include: - workbook name - must be visible to the RPG2SQL PC component - output file - must exist and have field layout that matches spreadsheet - special values for library include *LIBL and *CURLIB - output member - mamber name and add/replace option - if member does not exist, it will be created - header rows - number of rows in header of spreadsheet - will not be included - error report - whether to report errors or warnings - not yet implemented - host - name or address of PC where RPG2SQL PC component is running - special value is *CURRENT, which will be converted to the IP address of the PC on which the terminal emulation is running (does not work in a batch job) At this time only character, zoned, and packed data types are supported. Empty cells will be converted to default values in the physical file. (Requires PC Component 1.0.47.)

August 2005

Version 1.45
  • Changed SQL_GetDelimFld, SQL_GetDelimFlt, and SQL_GetDelimRcd to return T4096String instead of TString. Added more field type constants to RPGSQLH for use with SQL_GetDelimFlt.

July 2005

Version 1.44
  • Added SQL_TCPTrace function for tracing TCP communications to file RPGSQLLOG.
    SQL_TCPTrace(1) enables logging.
SQL_TCPTrace(0) disables logging. (Default)
  • Added SQL_TCPMultBuff function for attempting to read multiple data buffers when using SQL_MoveFirsBuf, SQL_MoveNextBuf, SQL_MoveLastBuf, & SQL_MoveprevBuf.
  • SQL_TCPMultBuff(1) enables multibuffer.
  • SQL_TCPMultBuff(0) disables. (Default) Either function must be called before the SQL_Connect function. Note: Requires PC component V1.0.42 or above.

May 2005

Version 1.43
  • Added TCP_GetIPFromHost function, so that a fully-qualified domain name can be used, not just a dotted IP address. If a dotted IP address is passed in, it is left as is. The return values of TCP_GetIPFromHost are 0 if was successful, -11 if the fully-qualified domain name could not be found, and -12 if the 4-byte IP address could not be converted to dotted IP format.
  • The samples were changed to use TCP_GetIPFromHost in order to get the dotted IP address used to connect. COBOL samples were changed to use service program RPGSQL01R instead of RPGSQL02R, which does not have the newest features, including the Excel functions. The examples show how to use underscores in the procedure names, as well as variable-length character and binary parameters. COBOL copybooks RPGSQL1CB (for working storage) & RPGSQL2CB (some utility functions) were created. Build programs and COBOL programs were created in addition to the sample command and processing program.

January 2005

Version 1.42
  • Change service programs RPGSQL01R, RPGSQL02R, and RPGDDS01R so that they will have the same signature from one version of the product to the next. Customers who use these service programs will not need to recompile their programs after this version, unless they use new functions.

November 2004

Version 1.40
  • Added RPGDDS01R service program to binding directory so SQTEST25R and other samples will compile that use the DDS buffer service program. Also fixed SQTEST25R so that it will compile correctly. It was referring to SQL_ExtDDS* functions that have been renamed to SQLExtDDS.

September 2004

Version 1.39
  • Added XLS_GETDELIMREC function to retrieve the contents of a spreadsheet row into a delimited record buffer. See sample SQTEST29 for example usage. Requires RPG2SQL PC Server V1.0.36 or above.
  • Extended data buffer from 256 to 4096 for SQL_GetFldChrb and SQL_GetFldChr buffer retrieval functions.Resolved lockup issues on PC component when using SQL_GetDelimRcd function to retreive delimited values from a record buffer when tilde was used as a delimiter. Requires RPG2SQL PC Server V1.0.36 or above.

September 2004

Version 1.38
  • Added SQL_SetSQLDelim function for determining what value to use for delimiter. A tilde (~) is used as the default delimiter. A multiple character delimiter can be specified if desired. Requires RPG2SQL PC Server V1.0.34 or above.
  • Added SQLSetSQLDelim function to COBOL service program. This is the COBOL equivalent of SQL_SetSQLDelim. Added SQLNonBlocking function to COBOL service program. This is the COBOL equivalent of SQL_NonBlocking.

September 2004

Version 1.37
  • Insure that internal data buffer gets cleared before doing buffered read operations. This was causing problems when customers actually wanted to save and parse the returned record data in their own programs instead of using the RPG2SQL field parsing functions.

August 2004

Version 1.36
  • Major internal library enhancement. Added the ability to use non-blocking IP connections so that all SQL TCP/IP connections to the PC server can be set to be timed out after a selected number of seconds. Blocking connections are still the default so the SQL_NonBlocking function must be called before running the SQL_Connect function if non-blocking TCP/IP connections are desired. If 0 is passed for the timeout valuem the timeout value is defaulted to 60 seconds.

July 2004

Version 1.35
  • Fixed problem with SQL timeout truncating timeout values, thus causing SQL queries to prematurely timeout.

May 2004

Version 1.34
  • Added fully qualified library names to service programs bound to service programs RPGDDS01R and RPGSQL02R.

April 2004

Version 1.33
  • Added API'S for reading a binary PC file by buffer.
    • SQL_PCFileOpenB - Open PC file for binary read access.
    • SQL_PCFileClose - Close PC file.
    • SQL_PCFileGetB - Read next 2KB buffer from the opened PC file.
    • SQL_PCFileSize - Returns size in bytes of PC file.
  • Added binding directory as well as H spec to header of all sample programs. This will eliminate the need to use the special B CL programs to generate an RPG2SQL RPG program. All samples can now simply be compiled with option 14. in PDM. Binding directory name: RJSRPGSQL in library RJSRPGSQL.

March 2004

Version 1.32
  • Added sample program to write records to Domino database via NotesSQL ODBC driver. Added sample program to read records from Domino database via NotesSQL ODBC driver.

March 2004

Version 1.31
  • Added sample program to create a new account, address, contact and opportunity record via SalesLogix.

February 2004

Version 1.30
  • Added sample program to write email recip record to WinSpool Email Report Server access database WSPESR32.MDB.

January 2004

Version 1.29
  • Consolidated includes into RPGSQLH to avoid AS/400 SQL precompiler messages because the AS/400 SQL precompiler does not support nested copy include statements.

January 2004

Version 1.28
  • Added SQL_MKDir function for creating directories on the PC server.

January 2004

Version 1.27
  • Changed SCTEST05L to have the following check IF SQL_RTN <> 0 instead of IF SQL_RTN > 0 after the call to SQLEXTDDS. This will insure that we bail out if DDS for selected file is not retrieved correctly.

December 2003

Version 1.26
  • Added functions so programs can now read an entire SQL record into a DDS defined data structure or record structure without having to return each field individually. This was added to a separate service program named: RPGDDS01R.
    • SQLExtDDS - Extract DDS field information from AS/400 File definition for use with SQLExtRecDDS.
    • SQLExtRecDDS - Extract record data returned from the delimited record buffer to the selected DDS record structure. This assumes that the field names are returned from the SQL query in the same relative field order as the DDS definition. Ex: If customer number is the first field returned from the SQL query the customer field should be the first field defined in the DDS defined record structure.
  • Added COBOL sample to read SQL Server database and extract record data to DDS defined record via SQLExtRecDDS- SCTEST05.
  • Added initial RPG2Excel functionality.

October 2003

Version 1.25
  • Added Subfile Inquiry Interactive Samples. SQTEST23 - Query Records from Name/Address. Table with MS Access Driver. SQTEST24 - Query Records from Titles Table in Microsoft SQL Server PUBS database.

July 2003

Version 1.24
  • Added logic to service program and to the PC server component to handle multiple TCP/IP data packets being sent from the AS/400. Apparently the original data packet can potentially arrive as multiple packets to the PC server component, so the new logic will handle this scenario. Requires RPG2SQL PC Server V1.0.18 or above. Extended SQL_Quote buffer size from 2048 to 4096 bytes.

July 2003

Version 1.23
  • Added COBOL sample to read SQL Server database SCTEST04
  • Fixed COBOL service program to correctly process returned dates in MM/DD/YY format returned by SQLGetFldDat2B.

July 2003

Version 1.22
  • Added SQL_LastErrNum2 function to return last ADO error message as a numeric value. SQL_LastErrNum returns the error number as a string. Added support for calling stored procedures with parms and also allow the return of parms or recordsets from SP calls.
    • SQL_RunSP - Run SP with parms.
    • SQL_RunSPSelect - Run SP and rtn recordset.
    • SQL_AddParm - Add SP parm.SQL_GetParm - Read return SP parm.
    • SQL_ClearParms - Clear all SP parms.
    • SQL_DBCloseRS - Close recordset.

June 2003

Version 1.21
  • Added SQL_RunSPBasic to allow users to call SQL stored procedures and check for a successful return code. Soon we will add the ability to call stored procedures that can return parameter values as well as recordsets when a stored procedure is run.

June 2003

Version 1.20
  • Added SQL_Quote function to allow users to take a string with a single quote and make sure it gets padded to two single quotes before performing a data insert via SQL. Otherwise strings with single quotes will cause an insert or update failure.

April 2003

Version 1.19
  • Added RPGSQL02R service program wrapper for allowing COBOL programmers to use the RPG2SQL functionality. Added initial COBOL sample programs SCTEST01 - SCTEST03 to source file SOURCE. Also added NAMEADDR2 file definition.

April 2003

Version 1.18
  • Soft coded ASCII/EBCDIC translation tables so users can set the tables to match their own systems.

April 2003

Version 1.17
  • Added SQL Server transaction control functions.
    • SQL_BeginTran - Begin transaction.
    • SQL_CommitTran - Commit transaction.
    • SQL_Rollback - Rollback transaction.
  • Added SQL Server samples:
    • SQTEST10 - Insert recs to SQL Server table.
    • SQTEST11 - Read Recs from SQL Server table and display to user.
    • SQTEST12 - Insert recs to SQL Server table using committment control. This program illustrates the use of the SQL_BeginTran, SQL_CommitTran and SQL_Rollback functions

March 2003

Version 1.16
  • Created SQTEST09 sample to read PF and write all records to Excel spreadsheet.

January 2003

Version 1.15
  • Added DOCPING utility command so users can PING TCP/IP host printers and systems.

December 2002

Version 1.14
  • Added new PC file management routines. SQL_PCFileEmpty - Create empty PC file.

December 2002

Version 1.13
  • Added new PC file management routines. SQL_PCFileRen - Renames PC file.

December 2002

Version 1.12
  • Make sure service program adds the RJSRPGSQL library to the library list, so the access code checking does not fail on program PRDINFO4 not being found.

December 2002

Version 1.11
  • Re-added license check and status message.

December 2002

Version 1.10
  • Temporarily removed license check from RPG service program RPGSQL01R. V1.08 test version was not working correctly.

December 2002

Version 1.09
  • Re-added license check and status message.

December 2002

Version 1.08
  • Temporarily removed license check from RPG service program RPGSQL01R.

December 2002

Version 1.07
  • Created SQL socket test program SQLSOCKET.

December 2002

Version 1.06
  • Added SQLTEST10 sample to test reading a CSV file to a PF via SQL.

2002-11-25

Version 1.05
  • Added NEW buffered database functions to read back the entire record buffer. This enhances performance when doing bulk record reads from a SQL server or access database.
    • SQL_MoveFirsBuf - Move to first record.
    • SQL_MoveNextBuf - Move to next record.
    • SQL_MovePrevBuf - Move to previous record.
    • SQL_MoveLastBuf - Move to last record.
    • SQL_GetFldChrB - Get character field from record buffer.
    • SQL_GetFldNumB - Get numeric field from record buffer.
    • SQL_GetFldDatB - Get date field from record buffer when already formatted in ISO format.
  • Added SQLTEST08 sample to read all records from the IRPGSQL.MDB table to file SQLTEST8PF in QTEMP. This tests the new buffered reading commands.
  • Added SQLTEST09 sample to test the new PC file management routines.
    • SQL_PCFileCopy - Copy PC file.
    • SQL_PCFileDel - Delete PC file.
    • SQL_PCFileExist - Does file exist ?
  • Created new buffered record retrieval function which uses the ADO GetString method to retrieve a delimited record.
    • SQL_GetStrRow - Retrieve next record in delimited string format.
  • Created function to convert MM/DD/YYYY dates into ISO format when a record is retrieved by the SQL_GetStrRow function.
    • SQL_GetFldDat2B - Get date field from record buffer when already formatted in ISO format.
  • Created SQLDATE1C to convert MM/DD/YYYY dates into ISO format.

November 2002

Version 1.04
  • Added SQLTEST06 sample to create a new spreadsheet via ADO.
  • Added SQL_PCFileExist function to check if a PC file exists.
  • Added SQL_PCFileDel function to delete a PC file if it exists.
  • Added SQL_PCFileCopy function to copy a PC file to a selected destination PC file.
  • Added AS/400 access code checking.
  • Added SQLUPG command to upgrade settings to new library if installed.

November 2002

Version 1.03
  • Added SQL_GetFldDat,SQL_GetFldNum and SQL_GetFldChr field retrieval function calls. Characters fields are returned the same as the SQL_GetField function. Dates are returned in *ISO timestamp format. Numeric fields are returned as packed 30,9.

October 2002

Version 1.02
  • Changed command SQLTEST01 to call SQLTEST01C instead of directly calling SQLTEST01R.Changed command SQLTEST03 to call SQLTEST03C instead of directly calling SQLTEST03R.

October 2002

Version 1.01

INITIAL DEVELOPMENT RELEASE

 

Back to Document Management Products